--exec sp_fab_data 1138,0,'0'
alter procedure sp_fab_data ( @processStep_Id INT, @baseproduct_id int, @default_prompt varchar(50))
as 
begin
	declare @fsl_qty_in int
	declare @fgt_group_name varchar(50)
	declare @processStep_table Table (ProcessStep_Id int)
	declare @results Table (FSL_Qty_In int, FGT_Group_name varchar(50))
	
	
	if ( @processStep_Id <> 0 ) 
	begin
		insert into @processStep_table (ProcessStep_Id) values ( @processStep_Id )
	end
	else if ( @baseproduct_id <> 0 and @processStep_Id = 0 ) 
	begin 
		insert into @processStep_table select p.processstep_id from product p, process_step ps where p.processstep_id = ps.processstep_id and p_parent_product_id =  @baseproduct_id and p_type is null and ps_type_name = 'fab'
	end
	else if ( @baseproduct_id = 0 and @processStep_Id = 0) 
	begin 
		insert into @processStep_table select p.processstep_id from product p, process_step ps where p.processstep_id = ps.processstep_id and p_type is null and ps_type_name = 'fab'
	end 

	insert into @results ( FSL_Qty_In, FGT_Group_name ) 
	select  sum(FSL.FSL_Qty_In) , FGT.FGT_Group_Name from Fab_Stage_Lot FSL, Fab_Group_Table FGT, Fab_Group_Stage_Table FGST, Lot_table LT
	where FGST.Stage_id = FSL.Stage_id and FGST.Group_id = FGT.Group_id and FSL.lot_id = LT.Lot_id and LT.Lot_Current_status = 'Running' and
	FSL.TIMESTAMP = ( SELECT MAX(FSL1.TIMESTAMP) FROM FAB_STAGE_LOT FSL1 WHERE FSL1.LOT_ID = LT.LOT_ID ) AND  LT.ProcessStep_ID in (select ProcessStep_Id from @processStep_table)
	group by FGT.FGT_Group_Name
	UNION --BY DEFAULT ASSIGNE GROUP NAME '10' FOR THOSE LOT FOR WHICH GROUP NAME IS NOT DEFINED.
	select  sum(FSL.FSL_Qty_In) , 10
	from Fab_Stage_Lot FSL, Lot_table LT
	where FSL.lot_id = LT.Lot_id and LT.Lot_Current_status = 'Running' and
	FSL.STAGE_ID NOT IN ( SELECT FGST.STAGE_ID FROM Fab_Group_Table FGT, Fab_Group_Stage_Table FGST WHERE FGT.GROUP_ID = FGST.GROUP_ID AND FGT.PROCESSSTEP_ID in (select ProcessStep_Id from @processStep_table) ) AND
	FSL.TIMESTAMP = ( SELECT MAX(FSL1.TIMESTAMP) FROM FAB_STAGE_LOT FSL1 WHERE FSL1.LOT_ID = LT.LOT_ID ) AND  LT.ProcessStep_ID in (select ProcessStep_Id from @processStep_table)

	DELETE FROM @results WHERE FSL_Qty_In IS NULL

	--select * from @results
	
	select SUM( FSL_Qty_In ) AS FSL_Qty_In , FGT_Group_name from @results GROUP BY FGT_Group_name
	
end